package com.fly.estore.dao.Impl;

import com.fly.estore.dao.BaseDAO;
import com.fly.estore.dao.ProductDAO;
import com.fly.estore.domain.ProductBrand;
import com.fly.estore.domain.ProductCatalog;
import com.fly.estore.domain.ProductInfo;
import org.apache.commons.io.output.StringBuilderWriter;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Fly on 2016/4/21.
 */
@Repository("ProductDAO")
public class ProductDAOImpl extends BaseDAO<ProductInfo> implements ProductDAO{


    public int addProduct(final ProductInfo productInfo) {
        int update = 0;
        StringBuilder sql = new StringBuilder();
        sql.append("insert into product_info(product_id, product_name, product_code, product_catalog_id, product_brand_id, product_intro, description, create_id, create_time)")
                .append("values (?, ?, ?, ?, ?, ?, ?, ?, ?)");
        update = getJdbcTemplate().update(sql.toString(), new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int i = 1;
                ps.setString(i++, productInfo.getProductId());
                ps.setString(i++, productInfo.getProductName());
                ps.setString(i++, productInfo.getProductCode());
                ps.setString(i++, productInfo.getCatalog().getProductCatalogId());
                ps.setString(i++, productInfo.getBrand().getProductBrandId());
                ps.setString(i++, productInfo.getProductIntro());
                ps.setString(i++, productInfo.getDescription());
                ps.setString(i++, productInfo.getCreateId());
                ps.setString(i++, productInfo.getCreateTime());
            }
        });
        return update;
    }

    public int updateProduct(final ProductInfo productInfo) {
        int update = 0;
        StringBuilder sql = new StringBuilder("update product_info set");
        sql.append(" product_name = ?,")
                .append(" product_code = ?,")
                .append(" product_catalog_id = ?,")
                .append(" product_brand_id = ?,")
                .append(" product_intro = ?,")
                .append(" description = ?,")
                .append(" last_update_id = ?,")
                .append(" last_update_time = ?")
                .append(" where product_id = ?");
        update = getJdbcTemplate().update(sql.toString(), new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int i = 1;
                ps.setString(i++, productInfo.getProductName());
                ps.setString(i++, productInfo.getProductCode());
                ps.setString(i++, productInfo.getCatalog().getProductCatalogId());
                ps.setString(i++, productInfo.getBrand().getProductBrandId());
                ps.setString(i++, productInfo.getProductIntro());
                ps.setString(i++, productInfo.getDescription());
                ps.setString(i++, productInfo.getCreateId());
                ps.setString(i++, productInfo.getCreateTime());
                ps.setString(i++, productInfo.getProductId());
            }
        });
        return update;
    }

    public int deleteById(final String productId) {
        int update = 0;
        String sql = "delete from product_info where product_id = ?";
        update = getJdbcTemplate().update(sql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, productId);
            }
        });
        return update;
    }

    public ProductInfo getById(String productId) {
        ProductInfo productInfo = new ProductInfo();
        String sql = "select * from product_info where product_id = ?";
        List<ProductInfo> resultList = getJdbcTemplate().query(sql, new BasicProductRowMapper(), new Object[]{productId});
        if (!CollectionUtils.isEmpty(resultList)){
            productInfo = resultList.get(0);
        }
        return productInfo;
    }

    public List<ProductInfo> getList() {
        List<ProductInfo> productList = new ArrayList<ProductInfo>();
        String sql = "select * from product_info order by create_time desc";
        productList = getJdbcTemplate().query(sql, new BasicProductRowMapper());
        return productList;
    }

    public List<ProductInfo> queryList(String productName, String productCode, String productCatalogId, String productBrandId) {
        List<ProductInfo> productList = new ArrayList<ProductInfo>();
        StringBuilder sql = new StringBuilder();
        sql.append("select p.product_id, p.product_name, p.product_code, p.product_intro, p.description, p.create_id, p.create_time, p.last_update_id, p.last_update_time,")
                .append(" pc.product_catalog_id, pc.catalog_name,")
                .append(" pb.product_brand_id, pb.brand_name,")
                .append(" ifnull(sd.stockAmount, 0) as stockAmount")
                .append(" from product_info p")
                .append(" left join product_catalog pc on p.product_catalog_id = pc.product_catalog_id")
                .append(" left join product_brand pb on p.product_brand_id = pb.product_brand_id")
                .append(" left join (select sum(amount) as stockAmount, product_id from stock_detail group by product_id) sd on p.product_id = sd.product_id")
                .append(" where 1 = 1");
        List params = new ArrayList();
        if (StringUtils.isNotBlank(productName)){
            sql.append(" and p.product_name like ?");
            params.add("%" + productName + "%");
        }
        if (StringUtils.isNotBlank(productCode)){
            sql.append(" and p.product_code like ?");
            params.add("%" + productCode + "%");
        }
        if (StringUtils.isNotBlank(productCatalogId)){
            sql.append(" and pc.product_catalog_id = ?");
            params.add(productCatalogId);
        }
        if (StringUtils.isNotBlank(productBrandId)){
            sql.append(" and pb.product_brand_id = ?");
            params.add(productBrandId);
        }
        sql.append(" order by p.create_time desc");
        productList = getJdbcTemplate().query(sql.toString(), new DetailProductRowMapper(), params.toArray());
        return productList;
    }

    public int getProductCount() {
        StringBuilder sql = new StringBuilder();
        sql.append("select count(product_id) from product_info");
        int total = getJdbcTemplate().queryForObject(sql.toString(), Integer.class);
        return total;
    }

    public class BasicProductRowMapper implements RowMapper<ProductInfo> {
        public ProductInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
            ProductInfo productInfo = new ProductInfo();
            ProductCatalog catalog = new ProductCatalog();
            ProductBrand brand = new ProductBrand();

            productInfo.setProductId(rs.getString("product_id"));
            productInfo.setProductName(rs.getString("product_name"));
            productInfo.setProductCode(rs.getString("product_code"));
            productInfo.setProductIntro(rs.getString("product_intro"));
            productInfo.setDescription(rs.getString("description"));
            catalog.setProductCatalogId(rs.getString("product_catalog_id"));
            productInfo.setCatalog(catalog);
            brand.setProductBrandId(rs.getString("product_brand_id"));
            productInfo.setBrand(brand);
            productInfo.setCreateTime(rs.getString("create_time"));
            productInfo.setCreateId(rs.getString("create_id"));
            productInfo.setLastUpdateId(rs.getString("last_update_id"));
            productInfo.setLastUpdateTime(rs.getString("last_update_time"));
            return productInfo;
        }
    }

    public class DetailProductRowMapper implements RowMapper<ProductInfo> {
        public ProductInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
            ProductInfo productInfo = new ProductInfo();
            ProductCatalog catalog = new ProductCatalog();
            ProductBrand brand = new ProductBrand();

            productInfo.setProductId(rs.getString("product_id"));
            productInfo.setProductName(rs.getString("product_name"));
            productInfo.setProductCode(rs.getString("product_code"));
            productInfo.setProductIntro(rs.getString("product_intro"));
            productInfo.setDescription(rs.getString("description"));
            catalog.setProductCatalogId(rs.getString("product_catalog_id"));
            catalog.setCatalogName(rs.getString("catalog_name"));
            productInfo.setCatalog(catalog);
            brand.setProductBrandId(rs.getString("product_brand_id"));
            brand.setBrandName(rs.getString("brand_name"));
            productInfo.setBrand(brand);
            productInfo.setStockAmount(rs.getInt("stockAmount"));
            productInfo.setCreateTime(rs.getString("create_time"));
            productInfo.setCreateId(rs.getString("create_id"));
            productInfo.setLastUpdateId(rs.getString("last_update_id"));
            productInfo.setLastUpdateTime(rs.getString("last_update_time"));
            return productInfo;
        }
    }
}
